﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.OleDb;
using System.Collections;
using System.Data;
using System.Reflection;

namespace DAO
{
    public class DataProvider
    {
        public static bool FLAG = false;

        #region Attributes
        //private static string _strConnection =
        //    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
        //    + Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
        //    + "Database.accdb";
        private static string _strConnection =
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database.accdb;Persist Security Info=False";
        public static int _defaultNumber = 0;
        private static OleDbConnection _dbConnection = null;

        protected OleDbCommand _dbCommand = null;
        #endregion

        #region Properties
        public static string ConnectionString
        {
            get { return DataProvider._strConnection; }
            set { DataProvider._strConnection = value; }
        }
        #endregion

        public static void Connect()
        {
            try
            {
                _dbConnection = new OleDbConnection(_strConnection);
                _dbConnection.Open();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        public static void Disconnect()
        {
            if (null != _dbConnection && _dbConnection.State == System.Data.ConnectionState.Open)
            {
                _dbConnection.Close();
            }
        }

        protected OleDbDataReader ExcuteReader(string sqlString)
        {
            _dbCommand = new OleDbCommand(sqlString, _dbConnection);
            return _dbCommand.ExecuteReader();
        }

        protected void ExcuteNonQuery(string sqlString)
        {
            _dbCommand = new OleDbCommand(sqlString, _dbConnection);
            _dbCommand.ExecuteNonQuery();
        }

        protected object ExcuteScalar(string sqlString)
        {
            _dbCommand = new OleDbCommand(sqlString, _dbConnection);
            return _dbCommand.ExecuteScalar();
        }

        protected ArrayList ConvertDataReaderToArrayList(OleDbDataReader dr)
        {
            ArrayList lst = new ArrayList();
            object obj = null;
            while (dr.Read())
            {
                obj = GetDataRowFromDataReader(dr);
                lst.Add(obj);
            }
            return lst;
        }

        public DataTable ConvertListToDataTable<T>(List<T> items)
        {
            var tb = new DataTable(typeof(T).Name);

            PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (PropertyInfo prop in props)
            {
                Type t = GetCoreType(prop.PropertyType);
                tb.Columns.Add(prop.Name, t);
            }

            foreach (T item in items)
            {
                var values = new object[props.Length];
                for (int i = 0; i < props.Length; i++)
                {
                    values[i] = props[i].GetValue(item, null);
                }
                tb.Rows.Add(values);
            }
            return tb;
        }

        private Type GetCoreType(Type t)
        {
            if (null != t && IsNullable(t))
            {
                if (!t.IsValueType)
                {
                    return t;
                }
                else
                {
                    return Nullable.GetUnderlyingType(t);
                }
            }
            else
            {
                return t;
            }
        }

        private bool IsNullable(Type t)
        {
            return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
        }

        protected virtual object GetDataRowFromDataReader(OleDbDataReader dr)
        {
            return null;
        }
    }
}
